﻿CREATE VIEW dbo.PersonFull
AS
SELECT     SUBSTRING(dbo.Person.Personnummer, 11, 1) AS temp, dbo.Person.Id AS PersonId, dbo.Person.ValId, dbo.Person.Personnummer AS PersonPersonnummer, 
                      LEN(dbo.Person.Personnummer) AS length, CASE SUBSTRING(dbo.Person.Personnummer, 12, 1) 
                      % 2 WHEN 0 THEN '393BAE97-283C-4DAD-8CFD-C9139CA4C6B8' WHEN 1 THEN '4A6AE859-21F1-4A2B-A917-75A266D09ADD' END AS PersonKonID, 
                      CASE SUBSTRING(dbo.Person.Personnummer, 12, 1) % 2 WHEN 0 THEN 'Kvinna' WHEN 1 THEN 'Man' END AS PersonKon, dbo.PersonAge.Age AS PersonAlder, 
                      dbo.Person.Fornamn AS PersonFornamn, dbo.Person.Efternamn AS PersonEfternamn, dbo.Person.TelNr AS PersonTelNr, dbo.Person.MobTelNr AS PersonMobTelNr, 
                      dbo.Person.Epost AS PersonEpost, dbo.Person.Gatuadress AS PersonAdress, dbo.Person.PostNr AS PersonPostNr, dbo.Person.PostOrt AS PersonPostOrt, 
                      dbo.Person.Fritext AS PersonFritext, CASE (dbo.Person.Inaktiv) WHEN 0 THEN 'Aktiv' WHEN 1 THEN 'Inaktiv' END AS PersonStatus, CASE (dbo.Person.Inaktiv) 
                      WHEN 0 THEN '38A7CF73-0BFA-4F61-81E6-C1922F71F87D' WHEN 1 THEN '442F13BF-6ADA-43C1-BB88-828092B88C68' END AS PersonStatusID, 
                      dbo.PersonTyp.Benamning AS PersonTyp, dbo.PersonTyp.Id AS PersonTypID, dbo.Kurs.KursInfo AS KursTillfalleKursInfo, 
                      dbo.Kurs.Beskrivning AS KursTillfalleKursbeskrivning, dbo.KursTillfalle.Kursansvarig AS KursTillfalleKursansvarig, dbo.KursTillfalle.Sal AS KursTillfalleSal, 
                      dbo.KursTillfalle.Adress AS KursTillfalleAdress, dbo.KursTillfalle.Sluttid AS KursTillfalleSluttid, dbo.KursTillfalle.Starttid AS KursTillfalleStarttid, 
                      dbo.KursTillfalle.Kursdatum AS KursTillfalleDatum, dbo.KursTillfalle.Lokal AS KursTillfalleLokal, dbo.UppdragFull.UppdragFunktion, dbo.UppdragFull.UppdragPublikFunktion, dbo.UppdragFull.GruppNamn,
					  dbo.UppdragFull.ValkretsNamn, dbo.UppdragFull.ForsamlingNamn, dbo.UppdragFull.ValdistriktNamn
FROM         dbo.PersonAge RIGHT OUTER JOIN
                      dbo.Person LEFT OUTER JOIN
                      dbo.PersonPersonTyp ON dbo.Person.Id = dbo.PersonPersonTyp.PersonId AND dbo.PersonPersonTyp.ValId = dbo.Person.ValId LEFT OUTER JOIN
                      dbo.PersonTyp ON dbo.PersonPersonTyp.ValId = dbo.PersonTyp.ValId AND dbo.PersonPersonTyp.PersonTypId = dbo.PersonTyp.Id LEFT OUTER JOIN
                      dbo.PersonKursTillfalle ON dbo.Person.Id = dbo.PersonKursTillfalle.PersonId AND dbo.Person.ValId = dbo.PersonKursTillfalle.ValId LEFT OUTER JOIN
                      dbo.KursTillfalle ON dbo.PersonKursTillfalle.KursTillfalleId = dbo.KursTillfalle.Id AND dbo.KursTillfalle.ValId = dbo.PersonKursTillfalle.ValId LEFT OUTER JOIN
                      dbo.Kurs ON dbo.KursTillfalle.KursId = dbo.Kurs.Id AND dbo.KursTillfalle.ValId = dbo.Kurs.ValId ON dbo.PersonAge.Id = dbo.Person.Id LEFT OUTER JOIN
                      dbo.UppdragFull ON dbo.Person.Id = dbo.UppdragFull.PersonId AND dbo.Person.ValId = dbo.UppdragFull.ValId
GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPaneCount', @value = 2, @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'PersonFull';


GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane2', @value = N'nd
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "UppdragFull"
            Begin Extent = 
               Top = 499
               Left = 992
               Bottom = 630
               Right = 1181
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 30
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 6090
         Alias = 2070
         Table = 2880
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'PersonFull';








GO
EXECUTE sp_addextendedproperty @name = N'MS_DiagramPane1', @value = N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[55] 4[16] 2[30] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = -288
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Person"
            Begin Extent = 
               Top = 35
               Left = 510
               Bottom = 380
               Right = 852
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PersonPersonTyp"
            Begin Extent = 
               Top = 21
               Left = 246
               Bottom = 129
               Right = 397
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PersonTyp"
            Begin Extent = 
               Top = 43
               Left = 19
               Bottom = 151
               Right = 170
            End
            DisplayFlags = 280
            TopColumn = 1
         End
         Begin Table = "PersonKursTillfalle"
            Begin Extent = 
               Top = 0
               Left = 1180
               Bottom = 194
               Right = 1331
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "KursTillfalle"
            Begin Extent = 
               Top = 49
               Left = 1424
               Bottom = 308
               Right = 1575
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "Kurs"
            Begin Extent = 
               Top = 128
               Left = 1697
               Bottom = 381
               Right = 1848
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "PersonAge"
            Begin Extent = 
               Top = 185
               Left = 127
               Bottom = 328
               Right = 278
            E', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'VIEW', @level1name = N'PersonFull';









